import Router from '@koa/router';
import db from '../mysqlHelper.js';
import logger from '../logger.js';
import response from '../response.js';
import fs, { write } from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { getOpenId } from '../applet.js';
import moment from 'moment';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const router = new Router;
router.prefix('/api');

//获取违停和禁停区停车前3列表
// router.post(`/get/violation/top`, async (ctx) => {

//     //查询违停列列表
//     let sql = `SELECT COUNT(*) as count,enterpriseName FROM orderInfo WHERE isEndInAllowBlock=2   GROUP BY enterpriseName`;
//     let wtlb = await db.query(sql);
//     if (wtlb instanceof Error) {
//         logger.error(`get/violation/top error :` + wtlb.message);
//         ctx.body = response.fail(`get/violation/top error :` + wtlb.message);
//         return;
//     }
//     //在禁停区内还车列表
//     sql = `SELECT COUNT(*) as count,enterpriseName FROM orderInfo WHERE isEndInNoAllowBlock=2   GROUP BY enterpriseName`;
//     let jtlb = await db.query(sql);
//     if (jtlb instanceof Error) {
//         logger.error(`get/violation/top error :` + jtlb.message);
//         ctx.body = response.fail(`get/violation/top error :` + jtlb.message);
//         return;
//     }
//     ctx.body = response.success({ wtlb, jtlb });
// });
router.post(`/get/violation/top`, async (ctx) => {
    // 计算当前日期所属的周数
    let currentWeek = moment().format('YYYYWW');
    let tableName = `orderInfo_${currentWeek}`;

    // 查询违停列列表
    let sql = `SELECT COUNT(*) as count, enterpriseName FROM ${tableName} WHERE isEndInAllowBlock=2 GROUP BY enterpriseName`;
    let wtlb = await db.query(sql);
    if (wtlb instanceof Error) {
        logger.error(`get/violation/top error :` + wtlb.message);
        ctx.body = response.fail(`get/violation/top error :` + wtlb.message);
        return;
    }

    // 在禁停区内还车列表
    sql = `SELECT COUNT(*) as count, enterpriseName FROM ${tableName} WHERE isEndInNoAllowBlock=2 GROUP BY enterpriseName`;
    let jtlb = await db.query(sql);
    if (jtlb instanceof Error) {
        logger.error(`get/violation/top error :` + jtlb.message);
        ctx.body = response.fail(`get/violation/top error :` + jtlb.message);
        return;
    }

    ctx.body = response.success({ wtlb, jtlb });
});

// router.post(`/get/orderCount/day`, async (ctx) => {
//     let sql = `SELECT COUNT(*) as count,enterpriseName FROM orderInfo GROUP BY enterpriseName`;
//     //let sql = `SELECT COUNT(*) as count,enterpriseName FROM orderInfo WHERE DATE_FORMAT(startTime,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY enterpriseName`;
//     let res = await db.query(sql);
//     if (res instanceof Error) {
//         logger.error(`get/orderCount/day error :` + res.message);
//         ctx.body = response.fail(`get/orderCount/day error :` + res.message);
//         return;
//     }
//     ctx.body = response.success(res);
// });
router.post(`/get/orderCount/day`, async (ctx) => {
    // 计算当前日期所属的周数
    let currentWeek = moment().format('YYYYWW');
    let tableName = `orderInfo_${currentWeek}`;

    let sql = `SELECT COUNT(*) as count, enterpriseName FROM ${tableName} GROUP BY enterpriseName`;
    //let sql = `SELECT COUNT(*) as count, enterpriseName FROM ${tableName} WHERE DATE_FORMAT(startTime,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY enterpriseName`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/orderCount/day error :` + res.message);
        ctx.body = response.fail(`get/orderCount/day error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});

//获取当日超投巡查次数和超投次数
router.post(`/get/overInvestigate/day`, async (ctx) => {

    let total = 0
    //查询投超巡查次数
    //let sql = `SELECT COUNT(*) as count    FROM inspectionRecord WHERE DATE_FORMAT(createTime,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') `;
    let sql = `SELECT COUNT(*) as count    FROM inspectionRecord  `;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/overInvestigate/day error :` + res.message);
        ctx.body = response.fail(`get/overInvestigate/day error :` + res.message);
        return;
    }
    total = res[0].count;
    //查询超投次数
    // sql=`SELECT COUNT(*) as count,enterpriseName FROM inspectionRecord WHERE DATE_FORMAT(createTime,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') AND  result!="正常" GROUP BY enterpriseName`;
    sql = `SELECT COUNT(*) as count,enterpriseName FROM inspectionRecord WHERE  result!="正常" GROUP BY enterpriseName`;
    res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/overInvestigate/day error :` + res.message);
        ctx.body = response.fail(`get/overInvestigate/day error :` + res.message);
        return;
    }
    ctx.body = response.success({ total, overInvestigate: res });
});
//WHERE createTime BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
//获取近30日的按日分组的超投巡查次数
router.post(`/get/overInvestigate/30day`, async (ctx) => {
    let sql = `SELECT COUNT(*) as count, DATE_FORMAT(createTime,'%Y-%m-%d') as createTime 
FROM inspectionRecord 
WHERE createTime >= DATE_SUB(CURDATE(), INTERVAL 29 DAY) 
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')
`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/overInvestigate/30day error :` + res.message);
        ctx.body = response.fail(`get/overInvestigate/30day error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});

//获取近7天的按日分组的订单次数
// router.post(`/get/orderCount/7day`, async (ctx) => {
//     let sql = `SELECT COUNT(*) as count, DATE_FORMAT(startTime,'%Y-%m-%d') as createTime
// FROM orderInfo
// WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
// GROUP BY DATE_FORMAT(startTime,'%Y-%m-%d')
// `;
//     let res = await db.query(sql);
//     if (res instanceof Error) {
//         logger.error(`get/orderCount/7day error :` + res.message);
//         ctx.body = response.fail(`get/orderCount/7day error :` + res.message);
//         return;
//     }
//     ctx.body = response.success(res);
// });

router.post(`/get/orderCount/7day`, async (ctx) => {
    // 计算过去7天的所有周数
    let startDate = moment().subtract(6, 'days').startOf('day');
    let endDate = moment().endOf('day');
    let weeks = new Set();

    while (startDate.isBefore(endDate) || startDate.isSame(endDate, 'week')) {
        weeks.add(startDate.format('YYYYWW'));
        startDate.add(1, 'day');
    }

    let results = [];
    for (let week of weeks) {
        let tableName = `orderInfo_${week}`;
        let sql = `SELECT COUNT(*) as count, DATE_FORMAT(startTime,'%Y-%m-%d') as createTime
                   FROM ${tableName}
                   WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                   GROUP BY DATE_FORMAT(startTime,'%Y-%m-%d')`;
        let res = await db.query(sql);
        if (res instanceof Error) {
            logger.error(`get/orderCount/7day error :` + res.message);
            ctx.body = response.fail(`get/orderCount/7day error :` + res.message);
            return;
        }
        results = results.concat(res);
    }

    // 合并结果
    let finalResults = results.reduce((acc, curr) => {
        let existing = acc.find(item => item.createTime === curr.createTime);
        if (existing) {
            existing.count += curr.count;
        } else {
            acc.push(curr);
        }
        return acc;
    }, []);

    ctx.body = response.success(finalResults);
});

//获取24小时内的按厂商名称分组的订单
// router.post(`/get/orderCount/24hour`, async (ctx) => {
//     let sql = `SELECT COUNT(*) as count,enterpriseName FROM orderInfo WHERE startTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY enterpriseName`;
//     let res = await db.query(sql);
//     if (res instanceof Error) {
//         logger.error(`get/orderCount/24hour error :` + res.message);
//         ctx.body = response.fail(`get/orderCount/24hour error :` + res.message);
//         return;
//     }
//     ctx.body = response.success(res);
// });


router.post(`/get/orderCount/24hour`, async (ctx) => {
    // 计算当前日期所属的周数
    let currentWeek = moment().format('YYYYWW');
    let tableName = `orderInfo_${currentWeek}`;

    // 查询过去24小时的数据
    let sql = `SELECT COUNT(*) as count, enterpriseName 
               FROM ${tableName} 
               WHERE startTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR) 
               GROUP BY enterpriseName`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/orderCount/24hour error :` + res.message);
        ctx.body = response.fail(`get/orderCount/24hour error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});


async function mkdirsSync(dirname) {
    if (fs.existsSync(dirname)) {
        return true;
    } else {
        if (mkdirsSync(path.dirname(dirname))) {
            fs.mkdirSync(dirname);
            return true;
        }
    }
}
//上传图片到软件根目录下/View/images
router.post('/file/uploadImage', async (ctx) => {
    if (!ctx.request.files && !ctx.request.files.file) {
        ctx.status = 400;
        ctx.body = response.fail('No file uploaded');
        return;
    }

    let file = ctx.request.files.file;
    let reader = fs.createReadStream(file.filepath);

    let targetPath = path.join(__dirname, '../View/images/', file.originalFilename);
    let targetDir = path.dirname(targetPath);

    let res = await mkdirsSync(targetDir);
    if (!res) {
        ctx.body = response.fail('Failed to create directory');
        return;
    }
    let writer = fs.createWriteStream(targetPath);
    reader.pipe(writer);

    // 监听文件写入完成
    writer.on('finish', () => {
        console.log('File upload completed');
    });

    // 错误处理
    writer.on('error', (err) => {
        console.error('File upload failed:', err);
        ctx.status = 500;
        ctx.body = response.fail('File upload failed');
    });

    ctx.body = response.success({ url: `/images/${file.originalFilename}` });





});

//获取坐标点附件一公里范围内的车辆信息
router.post('/get/nearbyBicycle', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("Parameter cannot be empty");
        return;
    }
    if (body.lat == null) {
        ctx.body = response.fail("Missing required field latitude");
        return;
    }
    if (body.lon == null) {
        ctx.body = response.fail("Missing required field longitude");
        return;
    }
    let sql = `SELECT * FROM bicyclePos WHERE 
    (6371 * ACOS(COS(RADIANS(${body.lat})) * COS(RADIANS(bicycleLatitude)) * COS(RADIANS(bicycleLongitude)
     - RADIANS(${body.lon})) + SIN(RADIANS(${body.lat})) * SIN(RADIANS(bicycleLatitude))))<1`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/nearbyBicycle error :` + res.message);
        ctx.body = response.fail(`get/nearbyBicycle error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});
//获取坐标点附近指定距离范围内的车辆信息 距离单位为米
router.post('/get/nearbyBicycleByDistance', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("Parameter cannot be empty");
        return;
    }
    if (body.lat == null) {
        ctx.body = response.fail("Missing required field latitude");
        return;
    }
    if (body.lon == null) {
        ctx.body = response.fail("Missing required field longitude");
        return;
    }
    if (body.distance == null) {
        ctx.body = response.fail("Missing required field distance");
        return;
    }
    let sql = `SELECT * FROM bicyclePos WHERE 
    (6371 * ACOS(COS(RADIANS(${body.lat})) * COS(RADIANS(bicycleLatitude)) * COS(RADIANS(bicycleLongitude)
     - RADIANS(${body.lon})) + SIN(RADIANS(${body.lat})) * SIN(RADIANS(bicycleLatitude))))<${body.distance / 1000}`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/nearbyBicycleByDistance error :` + res.message);
        ctx.body =
            response.fail(`get/nearbyBicycleByDistance error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
}
);


//获取所有车辆数据 并且根据厂商进行分组统计
router.post('/get/bicycle/total', async (ctx) => {
    let body = ctx.request.body;
    let where = " where  1=1 "
    if (body) {

        if (body.bicycleState) {
            where += `and bicycleState=${body.bicycleState} `
        }
        if (body.workState) {
            where += `and workState=${body.workState} `
        }
    }



    let sql = `SELECT  COUNT(*) AS total,enterpriseName FROM bicycle ${where}  GROUP BY enterpriseName`;
    //  FROM bicycle`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`get/bicycle error :` + res.message);
        ctx.body = response.fail(`get/bicycle error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});



router.post('/getOpenId', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("Parameter cannot be empty");
        return;
    }
    if (body.code == null) {
        ctx.body = response.fail("Missing required field code");
        return;
    }
    let res = await getOpenId(body.code);
    if (res instanceof Error) {
        logger.error(`getOpenId error :` + res.message);
        ctx.body = response.fail(`getOpenId error :` + res.message);
        return;
    }
    ctx.body = response.success(res);
});


export { router };